package com.apes.fn.server.servicePlan.repository;

import com.apes.fn.crm.customerExpand.FnCustomerExpand;
import com.apes.fn.server.servicePlan.model.ServicePlan;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.data.domain.Pageable;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * param:zyc
 **/
public interface ServicePlanRepository extends CustomRepository<ServicePlan, String> {

//    @Query(value = "select t from ServicePlan t where t.serviceDept.id = :deptId and t.serviceDept.id = t.belongDept.id and t.state = 'confirm' " +
//            "and exists (select 1 from ServicePlanItem f where f.servicePlan = t.id and f.serviceState = true " +
//            "and f.product.productGroup.id <> '20' " +
//            "and f.saleQty > (f.qtyReceive + f.qtyEnd) )")
//    List<ServicePlan> findServicePlansForPDA(@Param("deptId") String deptId);

    @Query(value = "select t from ServicePlan t where  t.id in :list " )
    List<ServicePlan> findPlanForReceive(@Param("list") List list);
    @Query(value = "select t from ServicePlan t where not exists (select 1 from QuotationOrder s where s.sourcePlan = t.id)" )
    Page<ServicePlan> findAllByUnitQuotaionOrder(@Param("list") List list,Pageable pageable);

    List<ServicePlan> findServicePlanByInnerSourceFromAndInnerSourceFromType(String innerFrom,String innerType);

    List<ServicePlan> findServicePlanByFnCustomerExpandCarLicenseAndBelongDeptIdAndStateNotIn(String carLicense,String deptId,List state);

    @Query(value = " select t from ServicePlan t where t.belongDept = :dept and t.fnCustomerExpand = :fnCustomerExpand and t.confirmDate is not null " +
            " and t.state <> 'termination' " )
    List<ServicePlan> findQualityTestServicePlan(@Param("dept") Dept dept, @Param("fnCustomerExpand")FnCustomerExpand fnCustomerExpand);

    @Query(value = "select f from ServicePlan f left join ServicePlanItem j on f.id = j.servicePlan where j.parentServicePlanItem.id in :list and j.qtyEnd<>j.saleQty")
    List<ServicePlan> findServicePlan(@Param("list") List list);

    @Query(value = "select t from ServicePlan t where  t.id in :list " )
    List<ServicePlan> findTest(@Param("list") List list);

    @Query(value = " SELECT S FROM ServicePlan S  "
            + " WHERE S.lastUpdatedStamp > :times OR (S.id > :id AND S.lastUpdatedStamp = :times) ORDER BY S.lastUpdatedStamp, S.id ASC "
    )
    List<ServicePlan> synServiceplanForTimestamp(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query(value = "SELECT P FROM ServicePlan P WHERE P.fnCustomerExpand.id = :fnCustomerExpandId AND (P.state <>:state or P.createDate >= :createDate)")
    List<ServicePlan> findServerType(@Param("fnCustomerExpandId")String fnCustomerExpand,@Param("createDate")Date createDate,@Param("state")String state);


    @Query(value = "SELECT COUNT(*) WAIT_RECEIVE FROM SER_SERVICE_PLAN A,SER_SERVICE_PLAN_ITEM B WHERE A.ID = B.SERVICE_PLAN_ID AND A.STATE ='confirm' " +
            "AND A.BELONG_DEPT_ID = ?1 AND (B.SALE_QTY - B.QTY_RECEIVE - B.QTY_END) <> 0 " +
            "AND (SELECT D.PRODUCT_GROUP_ID FROM MAS_PRODUCT D WHERE D.ID = B.PRODUCT_ID) <>10", nativeQuery = true)
    List<Map> queryWaitReceive(String dept);

    List<ServicePlan> findAllByBelongDeptAndConfirmDateIsNull(Dept belongDept);

    List<ServicePlan> findOneBySourceFromOrderByCreateDateDesc(@Param("sourceFrom") String sourceFrom);

    ServicePlan findOneBySourceFrom(String sourceFrom);

    List<ServicePlan> findAllBySourceFrom(String sourceFrom);

    @Query(value = "SELECT P FROM ServicePlan P WHERE P.fnCustomerExpand.id = :fnCustomerExpandId AND P.state <> :states")
    List<ServicePlan> findFnCustomerExpandAndStateNot(String fnCustomerExpandId,String states);

    @Query(value = "SELECT LEVELID FROM MAS_QCCXPZ@DB_OLDERP WHERE PZCXID = :jekunModelCode", nativeQuery = true)
    List<String> findLEVELID(String jekunModelCode);

    @Query(value = "SELECT * FROM SER_SERVICE_PLAN A WHERE TO_CHAR(A.CREATE_DATE,'YYYY-MM-dd') = :day",nativeQuery = true)
    List<ServicePlan> findServicePlanByDay(String day);
}
